﻿CREATE PROCEDURE [dbo].[GoodsVsContragentsInsertEx]
	@GoodsId [int],
	@ContragentId [int],
	@GoodsCode [int],
	@GoodsName [varchar](150),
	@CountryName [varchar](20),
	@ManufacturerName [varchar](70),
	@Note [varchar](250),
	@MD [datetime],
	@MW [varchar](50)
WITH EXECUTE AS CALLER
AS
DECLARE @strMsg nvarchar(500), @intRet int, @intErrCode int
DECLARE @intNextId int
	SET NOCOUNT ON

IF (@GoodsId Is Null) OR (@ContragentId Is Null) OR (@GoodsCode Is Null) OR (@GoodsName Is Null)
BEGIN
	IF @@trancount > 0 ROLLBACK TRAN
	SELECT @intErrCode = 1, @strMsg = 'Неправильные параметры в вызове процедуры ' + Object_Name(@@Procid) + ' !'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

IF (ltrim(@GoodsName) = '')
BEGIN
	IF @@trancount > 0 ROLLBACK TRAN
	SELECT @intErrCode = 2, @strMsg = 'Незаполнено наименование товара у контрагента!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END
--если перекодировка уже есть, она просто не будет вставлена
/*IF EXISTS (SELECT TOP 1 ID FROM [CA].[GoodsVsContragents] 
WHERE ContragentId = @ContragentId AND GoodsCode = @GoodsCode 
AND GoodsName = @GoodsName 
AND ( (CountryName = @CountryName) OR (@CountryName Is Null AND CountryName Is Null) )
AND ( (@ManufacturerName = ManufacturerName) OR (@ManufacturerName Is Null AND ManufacturerName Is Null) )
AND ToDelete = 0 )
BEGIN
	IF @@trancount > 0 ROLLBACK TRAN
	SELECT @intErrCode = 3, @strMsg = 'Дублируется товар у контрагента!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END*/

IF NOT EXISTS (SELECT DepartmentId FROM PR.DepartmentOptions WHERE DepartmentId = 0 ) 
BEGIN
	IF @@trancount > 0 ROLLBACK TRAN
	SELECT @intErrCode = 4, @strMsg = 'Изменять данные справочников можно только в офисе!'
	RAISERROR (@strMsg, 11, @intErrCode)
	RETURN @intErrCode
END

IF NOT EXISTS (SELECT * FROM CA.Goods WHERE ID = @GoodsId AND ToDelete = 0)
BEGIN
	IF @@trancount > 0 ROLLBACK TRAN
	SELECT @intErrCode = 5, 
	@strMsg = 'Отсутствует товар с идентификатором ' + cast(@GoodsId as varchar(20)) + ' !'
	RAISERROR (@strMsg, 11, 1)
	RETURN @intErrCode
END

EXEC [dbo].[ProcessStatistic] @Note OUTPUT, @MD OUTPUT, @MW OUTPUT
--получение значения идентификатора
EXEC @intRet = dbo.GetNextCounterFor 'Common', @intNextId OUTPUT
IF @intRet <> 0 RETURN @intRet

	SET NOCOUNT OFF;

DECLARE @tmp int
SET @tmp = @ContragentId
DECLARE @tmp1 INT
DECLARE @tab TABLE (Id INT PRIMARY KEY CLUSTERED)
--находим родительский
SELECT @tmp1 = ParentContragentId FROM PR.ContragentVsContragents
WHERE ChildContragentId = @tmp
--Если нет значение, значит изначально родительский
IF (@tmp1 is null)
BEGIN 
	--SET @tmp1 = @tmp
	INSERT INTO @tab(Id) VALUES (@tmp)
	INSERT INTO @tab(Id)
	SELECT ChildContragentId FROM PR.ContragentVsContragents
	WHERE ParentContragentId = @tmp
END
ELSE
BEGIN
	INSERT INTO @tab(Id) VALUES (@tmp1)
	INSERT INTO @tab(Id)
	SELECT ChildContragentId FROM PR.ContragentVsContragents
	WHERE ParentContragentId = @tmp1
END
DECLARE @tab2 TABLE(Id INT PRIMARY KEY CLUSTERED)
DECLARE cur CURSOR FOR
SELECT Id FROM @tab
OPEN cur
FETCH NEXT FROM cur 
INTO @tmp
WHILE @@FETCH_STATUS = 0
	BEGIN
	IF NOT EXISTS (SELECT TOP 1 ID FROM [CA].[GoodsVsContragents] 
	WHERE ContragentId = @tmp AND GoodsCode = @GoodsCode 
	AND GoodsName = @GoodsName AND GoodsId=@GoodsId
	AND ( (CountryName = @CountryName) OR (@CountryName Is Null AND CountryName Is Null) )
	AND ( (@ManufacturerName = ManufacturerName) OR (@ManufacturerName Is Null AND ManufacturerName Is Null) )
	AND ToDelete = 0 )
	BEGIN
		INSERT INTO [CA].[GoodsVsContragents] ( Id, [GoodsId], [ContragentId], [GoodsCode], [GoodsName], [CountryName], [ManufacturerName], 
		[Note], [MD], [MW], ToDelete) 
		VALUES (@intNextId, @GoodsId, @tmp, @GoodsCode, @GoodsName, @CountryName, @ManufacturerName, 
		@Note, @MD, @MW, 0);
		
		INSERT INTO @tab2(Id) VALUES (@intNextId)

		EXEC @intRet = dbo.GetNextCounterFor 'Common', @intNextId OUTPUT
		IF @intRet <> 0 RETURN @intRet
	END
FETCH NEXT FROM cur 
INTO @tmp
END
	
/*SELECT Id, GId, GoodsId, ContragentId, GoodsCode, GoodsName, CountryName, ManufacturerName, 
Note, MD, MW, ToDelete
FROM CA.GoodsVsContragents WHERE (Id = @intNextId)*/
SELECT Id FROM @tab2
RETURN 0